package cn.edu.swu;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class DBUtils {
	private static String DB_DRIVER = "com.mysql.cj.jdbc.Driver";
	private static String DB_URL    = "jdbc:mysql://localhost:3306/items?useUnicode=true&characterEncoding=utf8";
	private static String DB_URL2    = "jdbc:mysql://localhost:3306/users?useUnicode=true&characterEncoding=utf8";
	private static String DB_USER   = "root";
	private static String DB_PASS   = "78954073";
	
	public static void insert(String sql) throws SQLException {
		excute(sql);
	}
	
	public static void delete(String sql) throws SQLException {
		excute(sql);
	}
	
	public static void update(String sql) throws SQLException {
		excute(sql);
	}
		
	private static void excute(String sql) throws SQLException {
		try {
			Class.forName(DB_DRIVER);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		
		try (Connection connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS)) {
			try (Statement statement = connection.createStatement()) {
				statement.execute(sql);
			}
		}
	}
	
	public static List<Item> getItems(String sql) throws SQLException {
		try {
			Class.forName(DB_DRIVER);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		
		List<Item> items = new ArrayList<Item>();
		
		try (Connection connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS)) {
			try (Statement statement = connection.createStatement()) {
				ResultSet rs = statement.executeQuery(sql);				
				while (rs.next()) {
					Item item = new Item();
					item.setId(rs.getInt("id"));
					item.setName(rs.getString("name"));
					item.setTemplate(rs.getString("template"));
					item.setType(rs.getString("type"));
					item.setPrice(rs.getInt("price"));
					item.setDescribe(rs.getString("describe"));
					item.setPics(rs.getString("pics"));
					items.add(item);
				}
			}
		}
		
		return items;
	}

	public static List<User> getUsers(String sql) throws SQLException{
		try {
			Class.forName(DB_DRIVER);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}

		List<User> users = new ArrayList<User>();

		try (Connection connection = DriverManager.getConnection(DB_URL2, DB_USER, DB_PASS)) {
			try (Statement statement = connection.createStatement()) {
				ResultSet rs = statement.executeQuery(sql);
				while (rs.next()) {
					User user = new User();
					user.setId(rs.getInt("id"));
					user.setName(rs.getString("name"));
					user.setpassword(rs.getString("password"));
					users.add(user);
				}
			}
		}

		return users;

	}

}
